using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace QUAN_LY_NGAN_HANG_CAU_HOI_TRAC_NGHIEM
{
    public class GiaoVienDAL
    {

        private Guid _MaSoGiaoVien;
        
        public Guid MaSoGiaoVien
        {
            get { return _MaSoGiaoVien; }
            set { _MaSoGiaoVien = value; }
        }

        private string _hoGiaoVien;

        public string HoGiaoVien
        {
            get { return _hoGiaoVien; }
            set { _hoGiaoVien = value; }
        }

        private string _tenGiaoVien;

        public string TenGiaoVien
        {
            get { return _tenGiaoVien; }
            set { _tenGiaoVien = value; }
        }
        private DateTime _ngaySinh;

        public DateTime NgaySinh
        {
            get { return _ngaySinh; }
            set { _ngaySinh = value; }
        }
        private string _cMND;

        public string CMND
        {
            get { return _cMND; }
            set { _cMND = value; }
        }
        private bool _phai;

        public bool Phai
        {
            get { return _phai; }
            set { _phai = value; }
        }
        private string _diaChi;

        public string DiaChi
        {
            get { return _diaChi; }
            set { _diaChi = value; }
        }
        private string _dienThoai;

        public string DienThoai
        {
            get { return _dienThoai; }
            set { _dienThoai = value; }
        }
        
        public static Guid InsertThongTinGiaoVien(GiaoVienDAL cn,TaiKhoanDAL cntk)
        {
            Guid id;
            using (var con = new SqlConnection(Globals.ConnectionString))
            {
                var cmd = new SqlCommand("usp_ThemGiaoVien", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MaSoGiaoVien", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@HoGiaoVien", SqlDbType.NVarChar, 50).Value = cn.HoGiaoVien;
                cmd.Parameters.Add("@TenGiaoVien", SqlDbType.NVarChar, 50).Value = cn.TenGiaoVien;
                cmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value = cn.NgaySinh;
                cmd.Parameters.Add("@CMND", SqlDbType.NChar,10).Value = cn.CMND;
                cmd.Parameters.Add("@Phai", SqlDbType.Bit).Value = cn.Phai;
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar,50).Value = cn.DiaChi;
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar, 10).Value = cn.DienThoai;
                cmd.Parameters.Add("@Quyen", SqlDbType.UniqueIdentifier).Value = cntk.Quyen;
                cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = cntk.Username;
                cmd.Parameters.Add("@Password", SqlDbType.VarChar, 10).Value = cntk.Password;
                con.Open();
                cmd.ExecuteNonQuery();
                id = (Guid)cmd.Parameters["@MaSoGiaoVien"].Value;
            }
            return id;
        }
        
        public void UpdateThongTinGiaoVien()
        {
            throw new System.Exception("Not implemented");
        }

        public static List<GiaoVienDAL> LayTatCaGiaoVien()
        {
            List<GiaoVienDAL> lst = new List<GiaoVienDAL>();
            using (SqlConnection cn = new SqlConnection(Globals.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("usp_LayTatCaGiaoVien", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                IDataReader rd = cmd.ExecuteReader();
                if (rd != null)
                {
                    while (rd.Read())
                    {
                        var en = new GiaoVienDAL();
                        en.MaSoGiaoVien = (Guid)rd["MaSoGiaoVien"];
                        en.CMND = (string)rd["CMND"];
                        en.DiaChi = (string)rd["DiaChi"];
                        en.DienThoai = (string)rd["DienThoai"];
                        en.HoGiaoVien = (string)rd["HoGiaoVien"];
                        en.NgaySinh = (DateTime)rd["NgaySinh"];
                        en.Phai = (bool)rd["Phai"];
                        en.TenGiaoVien = (string)rd["TenGiaoVien"];
                        lst.Add(en);
                    }
                }
            }
            return lst;
        }

       
        public static void UpdateThongTinGiaoVien(GiaoVienDAL cn, TaiKhoanDAL cntk)
        {
            using (var con = new SqlConnection(Globals.ConnectionString))
            {
                var cmd = new SqlCommand("usp_CapNhatGiaoVien", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Username", SqlDbType.NVarChar,50).Value = cntk.Username;
                cmd.Parameters.Add("@HoGiaoVien", SqlDbType.NVarChar, 50).Value = cn.HoGiaoVien;
                cmd.Parameters.Add("@TenGiaoVien", SqlDbType.NVarChar, 50).Value = cn.TenGiaoVien;
                cmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value = cn.NgaySinh;
                cmd.Parameters.Add("@CMND", SqlDbType.NChar, 10).Value = cn.CMND;
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 50).Value = cn.DiaChi;
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar, 10).Value = cn.DienThoai;
                cmd.Parameters.Add("@Password", SqlDbType.VarChar, 10).Value = cntk.Password;
                con.Open();
                cmd.ExecuteNonQuery();
               
            }
        }

        public static GiaoVienDAL LayThongTinGiaoVienTheoMaTaiKhoan(string MaTaiKhoan)
        {
            var cn = new GiaoVienDAL();
            using (var con = new SqlConnection(Globals.ConnectionString))
            {
                var cmd = new SqlCommand("usp_LayGiaoVienTheoMaTaiKhoan", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Username", SqlDbType.NVarChar,50).Value = MaTaiKhoan;
                con.Open();
                IDataReader rd = cmd.ExecuteReader();
                
                if (rd != null)
                {
                    rd.Read();
                    cn.MaSoGiaoVien = (Guid)rd["MaSoGiaoVien"];
                    cn.HoGiaoVien = (string)rd["HoGiaoVien"];
                    cn.TenGiaoVien = (string) rd["TenGiaoVien"];
                    cn.NgaySinh = (DateTime) rd["NgaySinh"];
                    cn.CMND = (string)rd["CMND"];
                    cn.DiaChi = (string)rd["DiaChi"];
                    cn.DienThoai = (string)rd["DienThoai"];
                }
            }
            return cn;
        }
    }
}